Imports System.Data.SqlClient
Public Class ComandosBanco
    Public Shared Erro As String
    Public Shared RetornoInteger As Integer
    Public Shared RetornoString As String

    Public Shared strconexao As String
    Shared conexao As SqlConnection
    Public Enum FecharBanco
        Sim = 0
        Nao = 1
    End Enum
    Public Sub New()

        strconexao = "Data Source=server;Initial Catalog=evol;Persist Security Info=True;User ID=sa;pwd="

        conexao = New SqlConnection(strconexao)
        'ParPadrao = vAutoEscola
    End Sub

#Region "ApenasExecuta"
    Friend Overloads Shared Sub ApenasExecuta(ByVal sql As String, ByVal Tipo As CommandType, Optional ByRef Parametros() As SqlParameter = Nothing, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim)
        Try
            Dim cmd As New SqlCommand(sql, conexao)
            cmd.CommandType = Tipo
            AbreBanco()
            MontaParametros(cmd, Parametros)
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            cmd = Nothing
            Parametros = Nothing
            If FecharBanco = ComandosBanco.FecharBanco.Sim Then
                FechaBanco(Parametros)
            End If
        Catch ex As SqlException
            Throw ex
        End Try

    End Sub

    'Friend Overloads Shared Sub ApenasExecutaPadrao(ByVal sql As String, ByVal Tipo As CommandType, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim)
    '    Try
    '        Dim cmd As New SqlCommand(sql, conexao)
    '        cmd.CommandType = Tipo
    '        AbreBanco()
    '        cmd.Parameters.Add(NomeParPadrao, ParTipoPadrao).Value = ParPadrao
    '        cmd.ExecuteNonQuery()
    '        If FecharBanco = ComandosBanco.FecharBanco.Sim Then
    '            FechaBanco()
    '        End If
    '    Catch ex As SqlException
    '        Throw ex
    '    End Try
    'End Sub


#End Region

#Region "ExecutaRetornaInteiro"
    Friend Overloads Shared Function ExecutaRetornaInteiro(ByVal sql As String, Optional ByVal Fechar As FecharBanco = FecharBanco.Sim) As Integer
        Try
            Dim cmd As New SqlCommand(sql, conexao)
            AbreBanco()
            Dim retorno As Object = cmd.ExecuteScalar()
            If Fechar = FecharBanco.Sim Then
                FechaBanco()
            End If
            If IsDBNull(retorno) = False Then
                Return CInt(retorno)
            Else
                Return 0
            End If

        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Function
    Friend Overloads Shared Function ExecutaRetornaObjeto(ByVal sql As String, Optional ByVal Fechar As FecharBanco = FecharBanco.Sim) As Object
        Try
            Dim cmd As New SqlCommand(sql, conexao)
            AbreBanco()
            Dim retorno As Object = cmd.ExecuteScalar()
            If Fechar = FecharBanco.Sim Then
                FechaBanco()
            End If
            If IsDBNull(retorno) = False Then
                Return retorno
            Else
                Return "-"
            End If

        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Function

    Friend Overloads Shared Function ExecutaRetornaInteiro(ByVal sql As String, ByVal Tipo As CommandType, Optional ByRef Parametros() As SqlParameter = Nothing, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim) As Integer
        Dim retorno As Integer = 0
        Try
            Dim cmd As New SqlCommand(sql, conexao)
            cmd.CommandType = Tipo
            'abastece o cmd com os parametros do array
            AbreBanco()
            MontaParametros(cmd, Parametros)
            retorno = CInt(cmd.ExecuteScalar)
            If FecharBanco = ComandosBanco.FecharBanco.Sim Then
                FechaBanco(Parametros)
            End If
        Catch ex As SqlException
            Throw ex
        End Try
        Return retorno
    End Function

    Friend Overloads Shared Function ExecutaRetornaInteiro(ByVal NomeParametro As String, ByVal ParametroErro As String, ByVal sql As String, ByVal Tipo As CommandType, Optional ByRef Parametros() As SqlParameter = Nothing, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim) As Integer
        Dim cmd As New SqlCommand(sql, conexao)
        Try
            AbreBanco()
            cmd.CommandType = Tipo
            'abastece o cmd com os parametros do array
            MontaParametros(cmd, Parametros)
            'retorno = CInt(cmd.Parameters("@Retorno").Value)
            cmd.ExecuteNonQuery()
            FechaBanco()
            RetornoInteger = CInt(cmd.Parameters.Item(NomeParametro).Value)
            If FecharBanco = ComandosBanco.FecharBanco.Sim Then
                FechaBanco(Parametros)
            End If
        Catch ex As SqlException
            Erro = cmd.Parameters.Item(ParametroErro).Value.ToString
            FechaBanco(Parametros)
            '    Comandos.EnviaEmail("ERRO PROCEDURE EXECUTARETNORNASTRING ", "SQL = " & sql & " <br> RETORNO = " & RetornoInteger, False)
            Throw ex
        End Try
        Return RetornoInteger
    End Function

#End Region

#Region "ExecutaRetornaString"
    Friend Overloads Shared Function ExecutaRetornaString(ByVal sql As String, ByVal Tipo As CommandType, ByVal NomeRetorno As String, ByVal ParametroErro As String, ByVal ParamArray Parametros() As SqlParameter) As String
        Dim retorno As String
        Try
            AbreBanco()
            Dim cmd As New SqlCommand(sql, conexao)
            cmd.CommandType = Tipo
            'abastece o cmd com os parametros do array
            MontaParametros(cmd, Parametros)
            'retorno = CInt(cmd.Parameters("@Retorno").Value)
            cmd.ExecuteScalar()
            FechaBanco()
            If IsDBNull(cmd.Parameters.Item(ParametroErro).Value) = False Then
                retorno = cmd.Parameters.Item(ParametroErro).Value.ToString
                MsgBox(retorno)
                'Comandos.EnviaEmail("ERRO PROCEDURE EXECUTARETNORNASTRING ", "SQL = " & sql & " <br> RETORNO = " & retorno, False)
            Else
                retorno = cmd.Parameters.Item(NomeRetorno).Value.ToString
            End If
            FechaBanco()
        Catch ex As SqlException
            Throw ex
            FechaBanco()
        End Try
        Return retorno
    End Function
    Friend Overloads Shared Function ExecutaRetornaString(ByVal sql As String, ByVal Tipo As CommandType, Optional ByRef Parametros() As SqlParameter = Nothing, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim) As String
        Dim retorno As String
        Try
            AbreBanco()
            Dim cmd As New SqlCommand(sql, conexao)
            cmd.CommandType = Tipo
            'abastece o cmd com os parametros do array
            MontaParametros(cmd, Parametros)
            'retorno = CInt(cmd.Parameters("@Retorno").Value)
            retorno = CStr(cmd.ExecuteScalar())
            If FecharBanco = ComandosBanco.FecharBanco.Sim Then
                FechaBanco(Parametros)
            End If

        Catch ex As SqlException
            Throw ex
        End Try
        Return retorno
    End Function


#End Region

#Region "ExecutaRetornaDataReader"

    Friend Overloads Shared Function ExecutaRetornaDataReader(ByVal sql As String, ByVal CommandType As CommandType) As SqlDataReader
        Dim dr As SqlDataReader
        Try
            Dim cmd As New SqlCommand(sql, conexao)
            cmd.CommandType = CommandType
            AbreBanco()
            dr = cmd.ExecuteReader()
        Catch ex As Exception
            Throw ex
        End Try
        Return dr
    End Function
    Friend Overloads Shared Sub ExecutaRetornaDataReader(ByRef dr As SqlDataReader, ByVal sql As String, ByVal CommandType As CommandType)
        Try
            Dim cmd As New SqlCommand(sql, conexao)
            cmd.CommandType = CommandType
            AbreBanco()
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Friend Overloads Shared Sub ExecutaRetornaDataReader(ByRef dr As SqlDataReader, ByVal sql As String, ByVal Tipo As CommandType, ByVal ParamArray Parametros() As SqlParameter)
        Try
            Dim cmd As New SqlCommand(sql, conexao)
            cmd.CommandType = Tipo
            MontaParametros(cmd, Parametros)
            AbreBanco()
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        Catch ex As SqlException
            'Comandos.MsgboxDeErro("ComandosBanco", "ExecuteReader", ex.ErrorCode, ex.Message)
            Throw ex
        End Try
    End Sub
    Friend Overloads Shared Function ExecutaRetornaDataReader(ByVal sql As String, ByVal Tipo As CommandType, ByVal ParamArray Parametros() As SqlParameter) As SqlDataReader
        Try
            Dim dr As SqlDataReader
            Dim cmd As New SqlCommand(sql, conexao)
            cmd.CommandType = Tipo
            MontaParametros(cmd, Parametros)
            AbreBanco()
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Return dr
        Catch ex As SqlException
            'Comandos.MsgboxDeErro("ComandosBanco", "ExecuteReader", ex.ErrorCode, ex.Message)
            Throw ex
        End Try
    End Function
#End Region

#Region "ExecutaRetornaDataSet"
    'Friend Overloads Shared Sub ExecutaRetornaDataSetPadrao(ByRef ds As DataSet, ByVal sql As String, ByVal Tipo As CommandType, ByVal ValorTipo As SqlDbType, ByVal NomeParametro As String, ByVal Parametro As Object)
    '    Try
    '        Dim da As New SqlDataAdapter(sql, conexao)
    '        da.SelectCommand.CommandType = Tipo
    '        da.SelectCommand.Parameters.Add(NomeParPadrao, ParTipoPadrao).Value = ParPadrao
    '        da.Fill(ds, "Temp")
    '        da.Dispose()
    '    Catch ex As SqlException
    '        'Comandos.MsgboxDeErro("ComandosBanco", "ExecuteReader", ex.ErrorCode, ex.Message)
    '        Throw ex
    '    End Try

    'End Sub
    Friend Overloads Shared Sub ExecutaRetornaDataSet(ByRef ds As DataSet, ByVal sql As String, ByVal Tipo As CommandType, ByVal ValorTipo As SqlDbType, ByVal NomeParametro As String, ByVal Parametro As Object)
        Try
            Dim da As New SqlDataAdapter(sql, conexao)
            da.SelectCommand.CommandType = Tipo
            da.SelectCommand.Parameters.Add(NomeParametro, ValorTipo).Value = Parametro
            da.Fill(ds, "Temp")
            da.Dispose()
        Catch ex As SqlException
            'Comandos.MsgboxDeErro("ComandosBanco", "ExecuteReader", ex.ErrorCode, ex.Message)
            Throw ex
        End Try

    End Sub
    Friend Overloads Shared Function ExecutaRetornaDataSet(ByVal sql As String) As DataSet
        Dim ds As DataSet = New DataSet
        Try
            Dim da As New SqlDataAdapter(sql, conexao)
            da.SelectCommand.CommandType = CommandType.Text
            da.Fill(ds, "Temp")
            da.Dispose()
        Catch ex As Exception
            Throw
        End Try
        Return ds
    End Function
    'Friend Overloads Shared Sub ExecutaRetornaDataSet(ByRef ds As DataSet, ByVal sql As String, ByVal Tipo As CommandType)
    '    'Dim dsLocal As New DataSet
    '    Try
    '        'ds = dsLocal
    '        Dim da As New SqlDataAdapter(sql, conexao)
    '        da.SelectCommand.CommandType = Tipo
    '        'abastece o adapter com os parametros
    '        da.Fill(ds)
    '        da.Dispose()
    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    'End Sub
    Friend Overloads Shared Sub ExecutaRetornaDataSet(ByRef ds As DataSet, ByVal sql As String, ByVal Tipo As CommandType, Optional ByRef Parametros() As SqlParameter = Nothing, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim)
        Try
            ds.Tables.Clear()
            Dim da As New SqlDataAdapter(sql, conexao)
            da.SelectCommand.CommandType = Tipo
            'abastece o adapter com os parametros
            MontaParametros(da, Parametros)
            da.Fill(ds, "Temp")
            da.Dispose()
            If FecharBanco = ComandosBanco.FecharBanco.Sim Then
                FechaBanco(Parametros)
            End If
        Catch ex As SqlException
            'Comandos.MsgboxDeErro("ComandosBanco", "ExecuteReader", ex.ErrorCode, ex.Message)
            Throw ex
        End Try
    End Sub
#End Region

#Region "ExecutaRetornaDataTable"
    Friend Overloads Shared Function ExecutaRetornaDataTable(ByVal sql As String, Optional ByRef Parametros() As SqlParameter = Nothing, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim) As DataTable
        Dim dt As New DataTable
        Try
            Dim da As New SqlDataAdapter(sql, conexao)
            da.SelectCommand.CommandType = CommandType.Text
            da.Fill(dt)
            da.Dispose()
            If FecharBanco = ComandosBanco.FecharBanco.Sim Then FechaBanco(Parametros)
        Catch ex As Exception
            Throw
        End Try
        Return dt
    End Function
    Friend Overloads Shared Sub ExecutaRetornaDataTable(ByRef dt As DataTable, ByVal sql As String, ByVal Tipo As CommandType, Optional ByRef Parametros() As SqlParameter = Nothing, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim)
        Try
            Dim da As New SqlDataAdapter(sql, conexao)
            da.SelectCommand.CommandType = Tipo
            'abastece o adapter com os parametros
            MontaParametros(da, Parametros)
            da.Fill(dt)
            da.Dispose()
            If FecharBanco = ComandosBanco.FecharBanco.Sim Then FechaBanco(Parametros)
        Catch ex As SqlException
            'Comandos.MsgboxDeErro("ComandosBanco", "ExecuterReades", ex.ErrorCode, ex.Message)
            Throw ex

            End Try
    End Sub
    'Friend Overloads Shared Sub ExecutaRetornaDataTablePadrao(ByRef dt As DataTable, ByVal sql As String, ByVal Tipo As CommandType, Optional ByRef Parametros() As SqlParameter = Nothing, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim)
    '    Try

    '        Dim da As New SqlDataAdapter(sql, conexao)
    '        da.SelectCommand.CommandType = Tipo
    '        da.SelectCommand.Parameters.Add(NomeParPadrao, ParTipoPadrao).Value = ParPadrao
    '        MontaParametros(da, Parametros)
    '        da.Fill(dt)
    '        da.Dispose()
    '        If FecharBanco = ComandosBanco.FecharBanco.Sim Then FechaBanco(Parametros)
    '    Catch ex As SqlException
    '        'Comandos.MsgboxDeErro("ComandosBanco", "ExecuterReades", ex.ErrorCode, ex.Message)
    '        Throw ex

    '    End Try
    'End Sub
#End Region

#Region "AbasteceCombo"
    Public Shared Sub AbasteceCombo(ByRef combo As ComboBox, ByVal sql As String, ByVal texto As String, ByVal valor As String, ByVal Tipo As CommandType, ByVal ParamArray Parametros() As SqlParameter)
        Try
            Dim C As New ComandosBanco
            Dim ds As New DataSet
            ExecutaRetornaDataSet(ds, sql, Tipo, Parametros)
            combo.DataSource = ds.Tables(0).DefaultView
            combo.DisplayMember = texto
            combo.ValueMember = valor
            combo.SelectedValue = -1
        Catch ex As Exception
            Throw ex
        Finally
            conexao.Close()
        End Try

    End Sub
    'Public Shared Sub AbasteceComboPadrao(ByRef combo As ComboBox, ByVal sql As String, ByVal texto As String, ByVal valor As String)
    '    Try
    '        Dim C As New ComandosBanco
    '        Dim ds As New DataSet
    '        ExecutaRetornaDataSetPadrao(ds, sql, CommandType.StoredProcedure, SqlDbType.Int, "@autoescola", vAutoEscola)
    '        combo.DataSource = ds.Tables(0).DefaultView
    '        combo.ValueMember = valor
    '        combo.DisplayMember = texto

    '        combo.SelectedValue = -1
    '        ds = Nothing
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        conexao.Close()
    '    End Try

    'End Sub
    Public Shared Sub AbasteceCombo(ByRef combo As ToolStripComboBox, ByVal sql As String, ByVal texto As String, ByVal valor As String, ByVal Tipo As CommandType, ByVal ParamArray Parametros() As SqlParameter)
        Try
            Dim C As New ComandosBanco
            Dim ds As New DataSet
            ExecutaRetornaDataSet(ds, sql, Tipo, Parametros)
            combo.ComboBox.DataSource = ds.Tables(0).DefaultView
            combo.ComboBox.DisplayMember = texto
            combo.ComboBox.ValueMember = valor
            combo.ComboBox.SelectedIndex = -1
        Catch ex As Exception
            Throw ex
        Finally
            conexao.Close()
        End Try

    End Sub
    Public Shared Sub AbasteceCombo(ByRef combo As ComboBox, ByVal sql As String, ByVal texto As String, ByVal valor As String, ByVal Tipo As CommandType, Optional ByVal FecharBanco As FecharBanco = ComandosBanco.FecharBanco.Sim)
        Try
            Dim C As New ComandosBanco
            Dim ds As New DataSet
            ExecutaRetornaDataSet(ds, sql, Tipo, , FecharBanco)
            combo.DataSource = ds.Tables(0).DefaultView
            combo.DisplayMember = texto
            combo.ValueMember = valor
            combo.SelectedIndex = -1
        Catch ex As Exception
            Throw ex
        Finally
            conexao.Close()
        End Try

    End Sub

#End Region


    Public Shared Function AbreBanco() As Boolean
        Try
            If conexao.State = ConnectionState.Closed Then
                conexao.Open()
            End If
        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Overloads Shared Function FechaBanco() As Boolean
        Try
            If conexao.State = ConnectionState.Open Then
                conexao.Close()
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Overloads Shared Function FechaBanco(ByRef Param() As SqlParameter) As Boolean
        Try
            Param = Nothing
            If conexao.State = ConnectionState.Open Then
                conexao.Close()
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Shared Sub Envialote(ByVal dt As DataTable, ByVal Tabela As String)
        Dim bc As New SqlBulkCopy(conexao)
        bc.DestinationTableName = Tabela
        AbreBanco()
        bc.WriteToServer(dt)
        FechaBanco()
    End Sub




#Region "Parametros"

    Public Shared Function CriarParametro(ByVal name As String, ByVal type As SqlDbType, ByVal value As Object, Optional ByVal Direcao As ParameterDirection = ParameterDirection.Input, Optional ByVal Tamanho As Integer = 0) As SqlParameter
        Dim param As New SqlParameter


        param.ParameterName = name
        param.SqlDbType = type
        param.Direction = Direcao
        If Direcao = ParameterDirection.Output Then
            param.Size = Tamanho
        End If
        If value Is Nothing Then
            param.Value = DBNull.Value
        Else
            If type = SqlDbType.VarChar And value.ToString().Length = 0 Then

                param.Value = DBNull.Value

            ElseIf type = SqlDbType.DateTime Then

                If value.ToString = "" Then

                    param.Value = DBNull.Value

                Else
                    param.Value = value
                End If


            End If

                param.Value = value
            End If


        Return param
    End Function

    Private Overloads Sub MontaParametros(ByRef cmd As SqlCommand, ByVal Tipo As Integer, ByVal ParamArray Parametros() As SqlParameter)
        If Not (Parametros Is Nothing) Then
            Dim param As SqlParameter
            For Each param In Parametros
                cmd.Parameters.Add(param)
            Next param
        End If
    End Sub

    Private Overloads Shared Sub MontaParametros(ByRef da As SqlDataAdapter, ByVal ParamArray Parametros() As SqlParameter)
        If Not (Parametros Is Nothing) Then
            Dim param As SqlParameter
            For Each param In Parametros
                da.SelectCommand.Parameters.Add(param)
            Next param
        End If
    End Sub

    Private Overloads Shared Sub MontaParametros(ByRef cmd As SqlCommand, ByVal ParamArray Parametros() As SqlParameter)
        Try
            If Not (Parametros Is Nothing) Then
                Dim param As SqlParameter
                For Each param In Parametros
                    cmd.Parameters.Add(param)
                Next param
            End If
        Catch ex As Exception
            Throw ex
        End Try
     
    End Sub

#End Region

End Class
